<?php
/**
 * GioCMS
 * 
 * LICENSE
 *
 * This source file is subject to the GNU GENERAL PUBLIC LICENSE Version 2 
 * that is bundled with this package in the file LICENSE.txt.
 * It is also available through the world-wide-web at this URL:
 * http://www.gnu.org/licenses/gpl-2.0.txt
 * If you did not receive a copy of the license and are unable to
 * obtain it through the world-wide-web, please send an email
 * to license@ninhgio.com so we can send you a copy immediately.
 * 
 * @copyright	Copyright (c) 2010-2011 GioCMS (http://cms.ninhgio.com)
 * @license		http://www.gnu.org/licenses/gpl-2.0.txt GNU GENERAL PUBLIC LICENSE Version 2
 * @author		NinhGio - ninhgio@gmail.com
 * @since		1.0
 */
class Modules_Core_Models_Mysql_User extends Modules_Core_Models_User
{
	public function getById($id)
	{
		$sql = 'SELECT * FROM ' . $this->_conn->_tablePrefix . 'core_user 
							WHERE user_id = ' . $this->_conn->escape($id) . ' LIMIT 1';
		$rs = $this->_conn->query($sql);
		$return = (0 == mysql_num_rows($rs)) ? null : $this->_conn->fetchAll($rs);
		return $return;
	}
	
	public function getByOpenID($url)
	{
		$sql = 'SELECT * FROM ' . $this->_conn->_tablePrefix . 'core_user as u
				INNER JOIN ' . $this->_conn->_tablePrefix . 'core_user_openid_assoc as assc
					ON u.user_id = assc.user_id
				WHERE assc.openid_url = "' . $this->_conn->escape($url) . '" LIMIT 1';
		$rs = $this->_conn->query($sql);
		$return = (0 == mysql_num_rows($rs)) ? null : $this->_conn->fetchAll($rs);
		return $return;
	}
	
	public function getByEmail($email, $userId = null)
	{
		$sql = 'SELECT * FROM ' . $this->_conn->_tablePrefix . 'core_user 
							WHERE email = "' . $this->_conn->escape($email) . '"';
		if ($userId) {
			$sql .= ' AND user_id != "' . $this->_conn->escape($userId) . '"';
		}
		$sql .= ' LIMIT 1';
		$rs = $this->_conn->query($sql);
		$return = (0 == mysql_num_rows($rs)) ? null : $this->_conn->fetchAll($rs);
		return $return;
	}
	
	public function getByUsername($username, $userId = null)
	{
		$sql = 'SELECT * FROM ' . $this->_conn->_tablePrefix . 'core_user 
							WHERE username = "' . $this->_conn->escape($username) . '"';
		if ($userId) {
			$sql .= ' AND user_id != "' . $this->_conn->escape($userId) . '"';
		}
		$sql .= ' LIMIT 1';
		$rs = $this->_conn->query($sql);
		$return = (0 == mysql_num_rows($rs)) ? null : $this->_conn->fetchAll($rs);
		return $return;
	}
	
	public function auth($username, $password, $adminSection = true)
	{
		$sql = 'SELECT * FROM ' . $this->_conn->_tablePrefix . 'core_user AS u
							WHERE u.username = "%s" AND u.password = md5(CONCAT("%s", u.salt))';
		$sql .= ' LIMIT 1';
		$sql = sprintf($sql, $this->_conn->escape($username), $this->_conn->escape(md5($password)));
		
		$rs = $this->_conn->query($sql);
		$return = (0 == $this->_conn->count($rs)) ? null : $this->_conn->fetchAll($rs);
		/**
		 * Free result 
		 */
		$this->_conn->freeResult($rs);
		
		return $return;
	}
	
	public function setLastLogin($user)
	{
		$sql = 'UPDATE ' . $this->_conn->_tablePrefix . 'core_user 
					SET last_login = "%s" WHERE user_id = "%s"';
		$sql = sprintf($sql, $this->_conn->escape($user['last_login']), 
							$this->_conn->escape($user['user_id']));
		$rs = $this->_conn->query($sql);
		/**
		 * Free result 
		 */
		$this->_conn->freeResult($rs);					
	}
	
	public function add($user)
	{
		$userId = $this->_conn->insert($user, $this->_conn->_tablePrefix . 'core_user');
		return $userId;	
	}
	
	public function changepassword($user)
	{
		$sql = 'UPDATE ' . $this->_conn->_tablePrefix . 'core_user 
					SET password = "%s" WHERE user_id = "%s"';
		$sql = sprintf($sql, $this->_conn->escape($user['password']), 
							$this->_conn->escape($user['user_id']));
		$rs = $this->_conn->query($sql);
		/**
		 * Free result 
		 */
		$this->_conn->freeResult($rs);					
	}
	
	public function find($condition = array(), $offset = null, $limit = null)
	{
		$sql = 'SELECT * FROM ' . $this->_conn->_tablePrefix . 'core_user AS u WHERE 1';
		
		if (isset($condition['username']) && $condition['username']) {
			$sql .= ' AND u.username = "%s"';
			$sql = sprintf($sql, $this->_conn->escape($condition['username']));
		}
		if (isset($condition['role_id'])) {
			$sql .= ' AND u.role_id = "' . $this->_conn->escape($condition['role_id']) . '"';
		}
		if (isset($condition['email']) && $condition['email']) {
			$sql .= ' AND u.email = "%s"';
			$sql = sprintf($sql, $this->_conn->escape($condition['email']));
		}
		if (isset($condition['code']) && $condition['code']) {
			$sql .= ' AND u.code = "%s"';
			$sql = sprintf($sql, $this->_conn->escape($condition['code']));
		}
		if (isset($condition['keyword']) && $condition['keyword']) {
			$sql .= ' AND (u.username LIKE "%' . $this->_conn->escape($condition['keyword']) . '%"
						OR u.fullname LIKE "%' . $this->_conn->escape($condition['keyword']) . '%")';
		}
		if (isset($condition['status']) && $condition['status']) {
			$sql .= ' AND u.status = "' . $this->_conn->escape($condition['status']) . '"';
		}
		$sql .= ' ORDER BY u.created_date ASC';
		
		if ($offset !== null && $limit !== null) {
			$sql .= ' LIMIT ' . (int)$offset . ',' . (int)$limit;
		}
		$rs = $this->_conn->query($sql);
		$users = array();
		while ($row = $this->_conn->fetchAll($rs)) {
			$users[] = $row;
		}
		/**
		 * Free result 
		 */
		$this->_conn->freeResult($rs);
		return $users;
	}
	
	public function count($condition = array())
	{
		$sql = 'SELECT COUNT(*) as num_users FROM ' . $this->_conn->_tablePrefix . 'core_user AS u  WHERE 1';

		if (isset($condition['username']) && $condition['username']) {
			$sql .= ' AND u.username = "%s"';
			$sql = sprintf($sql, $this->_conn->escape($condition['username']));
		}
		if (isset($condition['role_id'])) {
			$sql .= ' AND u.role_id = "' . $this->_conn->escape($condition['role_id']) . '"';
		}
		if (isset($condition['email']) && $condition['email']) {
			$sql .= ' AND u.email = "%s"';
			$sql = sprintf($sql, $this->_conn->escape($condition['email']));
		}
		if (isset($condition['code']) && $condition['code']) {
			$sql .= ' AND u.code = "%s"';
			$sql = sprintf($sql, $this->_conn->escape($condition['code']));
		}
		if (isset($condition['keyword']) && $condition['keyword']) {
			$sql .= ' AND (u.username LIKE "%' . $this->_conn->escape($condition['keyword']) . '%"
						OR u.fullname LIKE "%' . $this->_conn->escape($condition['keyword']) . '%")';
		}
		if (isset($condition['status']) && $condition['status']) {
			$sql .= ' AND u.status = "' . $this->_conn->escape($condition['status']) . '"';
		}
		$sql .= ' LIMIT 1';
		$rs = $this->_conn->query($sql);
		$row = $this->_conn->fetchAll($rs);
		/**
		 * Free result 
		 */
		$this->_conn->freeResult($rs);
		return $row['num_users'];
	}
	
	public function update($user)
	{
		$sql = 'UPDATE ' . $this->_conn->_tablePrefix . 'core_user 
					SET username = "%s", password = "%s", fullname = "%s", email = "%s", status = "%s",
						role_id = "%s", code = "%s"
					WHERE user_id = "%d"';
		$sql = sprintf($sql, $this->_conn->escape($user['username']),
							$this->_conn->escape($user['password']),
							$this->_conn->escape($user['fullname']),
							$this->_conn->escape($user['email']),
							$this->_conn->escape($user['status']), 
							$this->_conn->escape($user['role_id']),
							$this->_conn->escape($user['code']),
							$this->_conn->escape($user['user_id']));
		$rs = $this->_conn->query($sql);
		/**
		 * Free result 
		 */
		$this->_conn->freeResult($rs);
		return $this->_conn->affectedRows();	
	}
	
	public function updatePasswordFor($username, $password)
	{
		$sql = 'UPDATE ' . $this->_conn->_tablePrefix . 'core_user 
					SET password = "%s", code = ""
					WHERE username = "%s"';
		$sql = sprintf($sql, $this->_conn->escape($password),
							$this->_conn->escape($username));
		$rs = $this->_conn->query($sql);
		/**
		 * Free result 
		 */
		$this->_conn->freeResult($rs);
		return $this->_conn->affectedRows();	
	}
	
	public function delete($userId)
	{
		$sql = 'DELETE FROM ' . $this->_conn->_tablePrefix . 'core_user 
							WHERE user_id = ' . $this->_conn->escape($userId) . ' AND locked = 0';
		$rs = $this->_conn->query($sql);
		$affectRow = $this->_conn->affectedRows();
		$this->_conn->freeResult($rs);
		return $affectRow;
	}
}